package DAO.MySQL;

import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import DAO.DAOException;
import DAO.LeaveDAO;
import Model.EmployeeBean;
import Model.LeaveBean;

public class LeaveDAOImpl extends GenericDAO<LeaveBean> implements LeaveDAO {
	
	private static final String TARGET_TABLE = "leave_tb";	

	@Override
	protected LeaveBean parseResultSet(ResultSet rs) throws SQLException {
		LeaveBean leave=new LeaveBean();
		leave.setLeaveId(rs.getInt(1));
		leave.setEmployeeId(rs.getInt(2));
		leave.setLeavetype(rs.getInt(3));
		leave.setLeaveStartDate(rs.getDate(4).toString());
		leave.setLeaveEndDate(rs.getDate(5).toString());
		leave.setLeaveStatus(rs.getInt(6));
		leave.setLeaveReason(rs.getString(7));
		leave.setWorkDissemination(rs.getString(8));
		leave.setContactDetails(rs.getString(9));
		leave.setManagerComments(rs.getString(10));
		leave.setCreatedDate(rs.getDate(11).toString());
		leave.setModifiedDate(rs.getDate(12).toString());
		leave.setApprovedBy(rs.getString(13));
		
		return leave;
	}

	@Override
	public ArrayList<LeaveBean> findLeave() throws ClassNotFoundException,
			SQLException {
		// TODO Auto-generated method stub
		return ( findLeaveWith(" where delete_flag='0' ") );
	}

	@Override
	public ArrayList<LeaveBean> findLeaveWith(String condition)
			throws ClassNotFoundException, SQLException {
		String query = "select *  from " + TARGET_TABLE + " " + condition;
		System.out.println("Select query is "+query);
		return ( executeMultipleQuery(query) );
	}

	@Override
	public ArrayList<LeaveBean> findLeaveByEmployeeId(int inEmpID)
			throws ClassNotFoundException, SQLException {
		ArrayList<LeaveBean> leaveList= findLeaveWith(" where employee_id=" + inEmpID + " and leave_status!=3");
		 if(leaveList.size()>1)
		{
			return leaveList;
		}else
		{ return null;}
	}

	@Override
	public ArrayList<LeaveBean> findLeaveByManagerId(int inManID)
			throws ClassNotFoundException, SQLException {
		System.out.println("******calling findLeave in LeaveDAOImpl******");
		String query1 = "select employee_id from employee_tb where manager_id=" + inManID;
		String query2 = " where employee_id in (" + query1 + ") and leave_status=1 or leave_status=2";
		System.out.println("final query is "+query2);
		return findLeaveWith(query2);
	}


	@Override
	public void insertLeave(LeaveBean leave) throws ClassNotFoundException,
			DAOException, SQLException, Exception {
//String condition=" where _id="+ leave.getLeaveId() + "'";
		java.util.Date now = new java.util.Date();    
	     SimpleDateFormat sdf = new SimpleDateFormat( "yyyy-MM-dd" );   
	     String sdate=sdf.format(now).toString();  
		String field="employee_id," + 
					"leave_type," + 
					"leave_startdate," +  
					"leave_enddate," + 
					"leave_status," + 
					"leave_reason," + 
					"work_dissemination," + 
					"contact_details," + 
					"manager_comments," + 
					"leave_createddate," + 
					"leave_modifieddate," + 
					"approved_by ";
					
		
		String value=" values(" + leave.getEmployeeId()+ "," +
						leave.getLeavetype()+ ",'" +
						leave.getLeaveStartDate() + "','" +
						leave.getLeaveEndDate() + "'," + 
						1 + ",'" +
						leave.getLeaveReason() + "','" +
						leave.getWorkDissemination() + "','" +
						leave.getContactDetails() + "','" +
						leave.getManagerComments() + "','" +
						sdate+ "','" +
						sdate+ "','" +
						leave.getApprovedBy()+"')";
		
		String query = "insert into " + TARGET_TABLE+"("+field+")" + value;
		System.out.println("query is"+query);
		executeSingleUpdate(query);
	}

	@Override
	public void updateLeave(LeaveBean leave) throws ClassNotFoundException,
			DAOException, SQLException {
		String condition=" where leave_id="+ leave.getLeaveId()+" and employee_id="+leave.getEmployeeId();
		
		String setClause=" set leave_type=" + leave.getLeavetype() + "," + 
							"leave_startdate='" + leave.getLeaveStartDate() + "'," + 
							"leave_enddate='" + leave.getLeaveEndDate() + "'," + 
							"leave_status=" + leave.getLeaveStatus() + "," +
							"leave_reason='" + leave.getLeaveReason() + "'," +
							"work_dissemination='" + leave.getWorkDissemination() + "'," +
							"contact_details='" + leave.getContactDetails() + "'," +
												
							"leave_modifieddate='" + leave.getModifiedDate() + "'" ;
						
		
		String query = "update " + TARGET_TABLE + setClause + condition;
		System.out.println("Update query is "+query);
		executeSingleUpdate(query);
	}

	@Override
	public void deleteLeave(LeaveBean leave) throws ClassNotFoundException,
			DAOException {
		String condition=" where leave_id='"+ leave.getLeaveId() + "'";
		
		String query="delete " + TARGET_TABLE + condition;		
	}

	@Override
	public LeaveBean getLeaveByLeaveID(int leaveID, int employeeid) throws ClassNotFoundException, SQLException {
		ArrayList<LeaveBean> leaveList= findLeaveWith(" where employee_id=" + employeeid + " and leave_status!=3 and leave_id="+leaveID);
		 return leaveList.get(0);
	}
	
}

	
